
CREATE   PROCEDURE SP_CUSTOMER_TYPES_MIGRATION_PROC

AS
BEGIN
	DECLARE @CUSTOMER_ID INT
	DECLARE @CUSTOMER_TYPE_ID INT

	-- GET ID VALUE FOR CUSTOMER TYPE
	SELECT @CUSTOMER_TYPE_ID = C_TYPE_ID FROM CUSTOMER_TYPES WHERE C_TYPE_NAME = 'CUSTOMER'

	-- GET ALL CUSTOMERS TO SET RELATIONSHIP
	DECLARE CUSTOMER_CURSOR CURSOR FOR SELECT CUSTOMER_ID FROM CUSTOMER	

	OPEN CUSTOMER_CURSOR

	FETCH NEXT FROM CUSTOMER_CURSOR INTO @CUSTOMER_ID

	WHILE ( @@FETCH_STATUS = 0 )
	BEGIN
		-- CHECK WHETHER TYPE IS SET FOR THIS CUSTOMER. IF NOT, INSERT ENTRY INTO RELATIONSHIP TABLE  

		IF ( ( SELECT COUNT(*) FROM CUSTOMER_TYPE_RELATIONSHIP WHERE CUSTOMER_ID = @CUSTOMER_ID AND C_TYPE_ID = @CUSTOMER_TYPE_ID ) = 0 )
		BEGIN
			INSERT INTO CUSTOMER_TYPE_RELATIONSHIP ( CUSTOMER_ID, C_TYPE_ID ) VALUES ( @CUSTOMER_ID , @CUSTOMER_TYPE_ID )
		END

		SET @CUSTOMER_ID = NULL

		FETCH NEXT FROM CUSTOMER_CURSOR INTO @CUSTOMER_ID		
	END

	CLOSE CUSTOMER_CURSOR
	DEALLOCATE CUSTOMER_CURSOR

END
